using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Resources;

using ClassBuilderWizard.Interfaces;

namespace ClassBuilderWizard.DefaultPlugins
{
    internal class SQLScript
    {
        private DatabaseObject _DbObject;
        private Parameters _Parameters;

        public SQLScript(DatabaseObject dbo, Parameters pars)
        {
            this._DbObject = dbo;
            this._Parameters = pars;
        }
        /// <summary>
        /// Create the SQL script code
        /// </summary>
        public void CreateContent()
        {
            StringBuilder sb = new StringBuilder();

            StringBuilder AllParameters = new StringBuilder();
            StringBuilder ParameterList = new StringBuilder();
            StringBuilder ColumnList = new StringBuilder();
            StringBuilder SetParameterList = new StringBuilder();
            string identity = string.Empty;
            StringBuilder KeyParameters = new StringBuilder();
            StringBuilder KeyWhere = new StringBuilder();

            //loop through the column in the database object and create some lists
            foreach (DatabaseColumn dc in this._DbObject.Columns)
            {
                //@[[Name]] [[Type]], 
                if (!dc.IsReadOnly || dc.IsIdentity)
                    AllParameters.Append(SQLTemplates.PassedParameter.Replace("[[Name]]", dc.Name).Replace("[[Type]]", dc.DatabaseTypeDeclaration));
                
                if (!dc.IsIdentity)
                {
                    if (!dc.IsReadOnly)
                    {
                        //@[[Name]], 
                        ParameterList.Append(SQLTemplates.ParameterList.Replace("[[Name]]", dc.Name));

                        //[[[Name]]], 
                        ColumnList.Append(SQLTemplates.ColumnList.Replace("[[Name]]", dc.Name));

                        //[[[Name]]]=@[[Name]], 
                        SetParameterList.Append(SQLTemplates.SetParameterList.Replace("[[Name]]", dc.Name));
                    }
                }
                else
                {
                    //SET @[[Name]]=SCOPE_IDENTITY()
                    identity = SQLTemplates.SetIdentityParameter.Replace("[[Name]]", dc.Name);
                }

                if (this._DbObject.KeyColumns.Contains(dc))
                {
                    //@[[Name]] [[Type]], 
                    KeyParameters.Append(SQLTemplates.PassedParameter.Replace("[[Name]]", dc.Name).Replace("[[Type]]", dc.DatabaseTypeDeclaration));
                    //[[[Name]]]=@[[Name]] AND 
                    KeyWhere.Append(SQLTemplates.WhereWithParameter.Replace("[[Name]]", dc.Name));
                }
            }

            string newTableName = "[" + this._DbObject.Schema + "].[" + this._DbObject.Name + "]";

            AllParameters.Remove(AllParameters.Length - 2, 2); //remove last ", "
            ParameterList.Remove(ParameterList.Length - 2, 2); //remove last ", "
            ColumnList.Remove(ColumnList.Length - 2, 2); //remove last ", "
            SetParameterList.Remove(SetParameterList.Length - 2, 2); //remove last ", "
            KeyParameters.Remove(KeyParameters.Length - 2, 2); //remove last ", "
            KeyWhere.Remove(KeyWhere.Length - 5, 5); //remove last " AND "

            //create the drop scripts
            sb.Append(SQLTemplates.DropProc.Replace("[[SP_Name]]", this._DbObject.SelectStoredProcedure).Replace("[[Schema]]", this._DbObject.Schema));
            sb.Append(SQLTemplates.DropProc.Replace("[[SP_Name]]", this._DbObject.SelectAllStoredProcedure).Replace("[[Schema]]", this._DbObject.Schema));
            sb.Append(SQLTemplates.DropProc.Replace("[[SP_Name]]", this._DbObject.SaveStoredProcedure).Replace("[[Schema]]", this._DbObject.Schema));
            sb.Append(SQLTemplates.DropProc.Replace("[[SP_Name]]", this._DbObject.DeleteStoredProcedure).Replace("[[Schema]]", this._DbObject.Schema));

            //create the select script
            string select = SQLTemplates.SelectProc;
            select = select.Replace("[[SP_Name]]", this._DbObject.SelectStoredProcedure);
            select = select.Replace("[[Schema]]", this._DbObject.Schema);
            select = select.Replace("[[Params]]", KeyParameters.ToString());
            select = select.Replace("[[[Table_Name]]]", newTableName);
            select = select.Replace("[[Where]]", KeyWhere.ToString());

            //create the delete script
            string delete = SQLTemplates.DeleteProc;
            delete = delete.Replace("[[SP_Name]]", this._DbObject.DeleteStoredProcedure);
            delete = delete.Replace("[[Schema]]", this._DbObject.Schema);
            delete = delete.Replace("[[Params]]", KeyParameters.ToString());
            delete = delete.Replace("[[[Table_Name]]]", newTableName);
            delete = delete.Replace("[[Where]]", KeyWhere.ToString());

            //create the select all script
            string selectall = SQLTemplates.SelectAllProc;
            selectall = selectall.Replace("[[SP_Name]]", this._DbObject.SelectAllStoredProcedure);
            selectall = selectall.Replace("[[Schema]]", this._DbObject.Schema);
            selectall = selectall.Replace("[[[Table_Name]]]", newTableName);

            //create the save template
            string save = SQLTemplates.SaveProc;
            save = save.Replace("[[SP_Name]]", this._DbObject.SaveStoredProcedure);
            save = save.Replace("[[Schema]]", this._DbObject.Schema);
            save = save.Replace("[[Params]]", AllParameters.ToString());
            save = save.Replace("[[[Table_Name]]]", newTableName);
            save = save.Replace("[[Where]]", KeyWhere.ToString());
            save = save.Replace("[[Set_List]]", SetParameterList.ToString());
            save = save.Replace("[[Column_List]]", ColumnList.ToString());
            save = save.Replace("[[Param_List]]", ParameterList.ToString());
            save = save.Replace("[[Set_ID]]", identity);

            //put the individual scripts into the larger script
            sb.Append(select);
            sb.Append(delete);
            sb.Append(selectall);
            sb.Append(save);

            this._DbObject.ScriptDefinition = sb.ToString();
        }
    }
}
